IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[State]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[State]
(
id int NOT NULL PRIMARY KEY  IDENTITY(1,1),
name varchar(50),
numbPopulation int,
numDistricts int
); 
END

/*--------------------------------------------------------------*/

/*District*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[District]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[District]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
name varchar(255),
numbPopulation int,
stateId int,
FOREIGN KEY (stateId) REFERENCES [dbo].[State](id) 
); 
END

/*--------------------------------------------------------------*/

/*Party*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Party]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Party]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
name varchar(225),
senatorNumb int,
representitiveNumb int,
 
); 
END

/*--------------------------------------------------------------*/

/*User*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Users]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
User_Type int not null ,
name varchar(255),
userName varchar(255),
userPassword varchar(255),
password_salt varchar(255),
DistrictId int FOREIGN KEY (DistrictId) REFERENCES District(id),
StateId int FOREIGN KEY (StateId) REFERENCES State(id),
PartyId int FOREIGN KEY (PartyId) REFERENCES Party(id)

); 
END


/*--------------------------------------------------------------*/

/*Subject*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Subject]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Subject]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
title varchar(255),
content  varchar(225),
isActive int not null,
voteNumb int

); 
END
/*Answer*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Answer]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Answer]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
Text varchar(255),

); 
END
/*--------------------------------------------------------------*/
/*Subject_Vote*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Subject_Vote]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Subject_Vote]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
Subject_id int,
User_id  int,
Answer_id int,
FOREIGN KEY (Subject_id) REFERENCES subject(id),
FOREIGN KEY (User_id) REFERENCES users(id),
FOREIGN KEY (Answer_id) REFERENCES Answer(id),
); 
END
/*--------------------------------------------------------------*/


/*--------------------------------------------------------------*/

/*Question*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Question]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Question]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
Text varchar(255),
UserID int FOREIGN KEY (UserID) REFERENCES Users(id),
); 
END





/*--------------------------------------------------------------*/

/*Question_Vote*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Question_Votes]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Question_Vote]
(
User_ID int FOREIGN KEY (User_ID) REFERENCES Users(id),
QID int FOREIGN KEY (QID) REFERENCES Question(id),
AID int FOREIGN KEY (AID) REFERENCES Answer(id),
primary key(QID,AID,User_ID)

); 
END


/*--------------------------------------------------------------*/
